package com.nash.demands;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class Helper extends SQLiteOpenHelper {

  private static final int DB_VERSION = 41;
  private static final String DB_NAME = "demands";

  public static final String TBL_ANSWER = "answer";
  public static final String TBL_ANSWER_NAME = "NAME";
  public static final String TBL_ANSWER_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_ANSWER = "create table " + TBL_ANSWER + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_ANSWER_NAME + " varchar(255) NOT NULL collate nocase, "
      + TBL_ANSWER_IS_ARC + " BOOL NOT NULL DEFAULT 0)";

  public static final String TBL_AGENT = "agent";
  public static final String TBL_AGENT_NAME = "NAME";
  public static final String TBL_AGENT_ADDRESS = "ADDRESS";
  public static final String TBL_AGENT_PHONE = "PHONE";
  public static final String TBL_AGENT_ID_ANSWER = "ID_ANSWER";
  public static final String TBL_AGENT_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_AGENT = "create table " + TBL_AGENT + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_AGENT_NAME + " varchar(255) NOT NULL collate nocase, "
      + TBL_AGENT_ADDRESS + " VARCHAR(255) collate nocase, " + TBL_AGENT_PHONE + " VARCHAR(30), " + TBL_AGENT_ID_ANSWER + " INTEGER, " + TBL_AGENT_IS_ARC + " BOOL NOT NULL DEFAULT 0)";
  private static final String IDX_AGENT_1 = "agent_arc_name";
  private static final String CREATE_INDEX_AGENT_1="create index "+IDX_AGENT_1+" ON "+TBL_AGENT+"("+ TBL_AGENT_IS_ARC + "," + TBL_AGENT_NAME + ")";
  

  public static final String TBL_GOODS = "goods";
  public static final String TBL_GOODS_ID_PARENT = "ID_PARENT";
  public static final String TBL_GOODS_NAME = "NAME";
  public static final String TBL_GOODS_CODE = "CODE";
  public static final String TBL_GOODS_TREE_STR = "TREE_STR";
  public static final String TBL_GOODS_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_GOODS = "create table " + TBL_GOODS + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_GOODS_ID_PARENT + " integer not null," + TBL_GOODS_NAME
      + " varchar(255) NOT NULL collate nocase," + TBL_GOODS_CODE + " varchar(16)," + TBL_GOODS_TREE_STR + " varchar(50), " + TBL_GOODS_IS_ARC + " BOOL NOT NULL DEFAULT 0)";
  private static final String IDX_GOODS_1 = "goods_arc_tree";
  private static final String IDX_GOODS_2 = "goods_arc_name";
  private static final String CREATE_INDEX_GOODS_1="create index "+IDX_GOODS_1+" ON "+TBL_GOODS+"("+ TBL_GOODS_IS_ARC + "," + TBL_GOODS_TREE_STR + ")";
  private static final String CREATE_INDEX_GOODS_2="create index "+IDX_GOODS_2+" ON "+TBL_GOODS+"("+ TBL_GOODS_IS_ARC + "," + TBL_GOODS_NAME + ")";

  public static final String TBL_TU = "tu";
  public static final String TBL_TU_ID_GOODS = "ID_GOODS";
  public static final String TBL_TU_NAME = "NAME";
  public static final String TBL_TU_INBOX = "INBOX";
  public static final String TBL_TU_PRICE = "PRICE";
  public static final String TBL_TU_NOMER = "NOMER";
  public static final String TBL_TU_CATALOG = "CATALOG";
  public static final String TBL_TU_STOCK = "STOCK";
  public static final String TBL_TU_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_TU = "create table " + TBL_TU + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_TU_ID_GOODS + " integer NOT NULL, " + TBL_TU_NAME
      + " varchar(250)," + TBL_TU_INBOX + " integer, " + TBL_TU_PRICE + " numeric, " + TBL_TU_NOMER + " varchar(100), " + TBL_TU_CATALOG + " varchar(100), " + TBL_TU_STOCK + " integer not null default 0, "
      + TBL_TU_IS_ARC + " BOOL NOT NULL DEFAULT 0, foreign key (" + TBL_TU_ID_GOODS + ") references "+TBL_GOODS+"("+BaseColumns._ID+") on delete cascade on update cascade)";
  private static final String IDX_TU_1 = "tu_arc_name";
  private static final String CREATE_INDEX_TU_1="create index "+IDX_TU_1+" ON "+TBL_TU+"("+ TBL_TU_IS_ARC + "," + TBL_TU_NAME + ")";

  public static final String TBL_ROUTE = "route";
  public static final String TBL_ROUTE_DATE = "RDATE";
  public static final String TBL_ROUTE_AGENT = "ID_AGENT";
  private static final String CREATE_TABLE_ROUTE = "create table " + TBL_ROUTE + " ( " + BaseColumns._ID + " integer primary key autoincrement NOT NULL, " + TBL_ROUTE_DATE + " date NOT NULL, " + TBL_ROUTE_AGENT
      + " integer not null, foreign key (" + TBL_ROUTE_AGENT + ") references "+TBL_AGENT+"("+BaseColumns._ID+") on delete restrict on update cascade)";

  public static final String TBL_HOST = "host";
  public static final String TBL_HOST_NAME = "NAME";
  public static final String TBL_HOST_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_HOST = "create table " + TBL_HOST + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_HOST_NAME + " varchar(100) NOT NULL, " + TBL_HOST_IS_ARC
      + " boolean not null default 0)";

  public static final String TBL_OSNR = "osnr";
  public static final String TBL_OSNR_NAME = "NAME";
  public static final String TBL_OSNR_IS_ARC = "IS_ARC";
  private static final String CREATE_TABLE_OSNR = "create table " + TBL_OSNR + " ( " + BaseColumns._ID + " integer primary key NOT NULL, " + TBL_OSNR_NAME + " varchar(100) NOT NULL, " + TBL_OSNR_IS_ARC
      + " boolean not null default 0)";

  public static final String TBL_DEMAND = "demand";
  public static final String TBL_DEMAND_ID_AGENT = "ID_AGENT";
  public static final String TBL_DEMAND_DATE = "DATE"; // otgruzka
  public static final String TBL_DEMAND_DEMAND_DATE = "DEMAND_DATE";
  public static final String TBL_DEMAND_TS = "DATE_INSERT";
  public static final String TBL_DEMAND_ID_HOST = "ID_HOST";
  public static final String TBL_DEMAND_ID_OSNR = "ID_OSNR";
  public static final String TBL_DEMAND_INFO = "INFO";
  public static final String TBL_DEMAND_ADDIT = "ADDIT";
  private static final String CREATE_TABLE_DEMAND = "create table " + TBL_DEMAND + " ( " + BaseColumns._ID + " integer primary key autoincrement NOT NULL, " + TBL_DEMAND_ID_AGENT + " integer NOT NULL, "
      + TBL_DEMAND_DATE + " date not null," + TBL_DEMAND_DEMAND_DATE + " date not null," + TBL_DEMAND_TS + " DATETIME DEFAULT CURRENT_TIMESTAMP," + TBL_DEMAND_ID_HOST + " integer not null," + TBL_DEMAND_ID_OSNR
      + " integer not null," + TBL_DEMAND_INFO + " varchar(255)," + TBL_DEMAND_ADDIT + " integer not null default 0,"
      + " foreign key (" + TBL_DEMAND_ID_AGENT + ") references "+TBL_AGENT+"("+BaseColumns._ID+") on delete restrict on update cascade,"
      + " foreign key (" + TBL_DEMAND_ID_HOST + ") references "+TBL_HOST+"("+BaseColumns._ID+") on delete restrict on update cascade,"
      + " foreign key (" + TBL_DEMAND_ID_OSNR + ") references "+TBL_OSNR+"("+BaseColumns._ID+") on delete restrict on update cascade)";

  public static final String TBL_DEMAND_LINE = "demand_line";
  public static final String TBL_DEMAND_LINE_ID_DEMAND = "ID_DEMAND";
  public static final String TBL_DEMAND_LINE_ID_TU = "ID_TU";
  public static final String TBL_DEMAND_LINE_PRICE = "PRICE";
  public static final String TBL_DEMAND_LINE_AMOUNT = "AMOUNT";
  private static final String CREATE_TABLE_DEMAND_LINE = "create table " + TBL_DEMAND_LINE + " (" + BaseColumns._ID + " integer primary key autoincrement NOT NULL, " + TBL_DEMAND_LINE_ID_DEMAND + " integer NOT NULL, "
      + TBL_DEMAND_LINE_ID_TU + " integer NOT NULL, " + TBL_DEMAND_LINE_AMOUNT + " integer not null, " + TBL_DEMAND_LINE_PRICE + " numeric not null,"
      + " foreign key (" + TBL_DEMAND_LINE_ID_DEMAND + ") references "+TBL_DEMAND+"("+BaseColumns._ID+") on delete cascade on update cascade,"
      + " foreign key (" + TBL_DEMAND_LINE_ID_TU + ") references "+TBL_TU+"("+BaseColumns._ID+") on delete restrict on update cascade)";

  public static final String TBL_VERSIONS = "versions";
  public static final String TBL_VERSIONS_NAME = "NAME";
  public static final String TBL_VERSIONS_VALUE = "VALUE";
  private static final String CREATE_TABLE_VERSIONS = "create table " + TBL_VERSIONS + " ( " + TBL_VERSIONS_NAME + " varchar(24) primary key NOT NULL, " + TBL_VERSIONS_VALUE + " integer NOT NULL default 0)";

  public static final String TBL_OPTIONS = "options";
  public static final String TBL_OPTIONS_NAME = "NAME";
  public static final String TBL_OPTIONS_VALUE = "VALUE";
  private static final String CREATE_TABLE_OPTIONS = "create table " + TBL_OPTIONS + " ( " + TBL_OPTIONS_NAME + " varchar(24) primary key NOT NULL, " + TBL_OPTIONS_VALUE + " varchar(250))";

  public Helper(Context c) {
    super(c, DB_NAME, null, DB_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_ANSWER);
    db.execSQL(CREATE_TABLE_AGENT);
    db.execSQL(CREATE_INDEX_AGENT_1);
    db.execSQL(CREATE_TABLE_GOODS);
    db.execSQL(CREATE_INDEX_GOODS_1);
    db.execSQL(CREATE_INDEX_GOODS_2);
    db.execSQL(CREATE_TABLE_TU);
    db.execSQL(CREATE_INDEX_TU_1);
    db.execSQL(CREATE_TABLE_ROUTE);
    db.execSQL(CREATE_TABLE_DEMAND);
    db.execSQL(CREATE_TABLE_DEMAND_LINE);
    db.execSQL(CREATE_TABLE_HOST);
    db.execSQL(CREATE_TABLE_OSNR);
    db.execSQL(CREATE_TABLE_OPTIONS);

    db.execSQL(CREATE_TABLE_VERSIONS);
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_AGENT + "')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_ANSWER + "')");
    db.execSQL("insert into versions (name) values('debts')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_GOODS + "')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_HOST + "')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_OSNR + "')");
    db.execSQL("insert into versions (name) values('matrix')");
    db.execSQL("insert into versions (name) values('task')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_TU_MAIN + "')");
    db.execSQL("insert into versions (name) values('" + UpdateThread.VER_TU_OSTATOK + "')");
  }

  @Override
  public void onUpgrade(final SQLiteDatabase db, final int i, final int i1) {
    Log.w(Helper.class.getName(), "Upgrading database from version " + i + " to " + i1 );
    if (i < 38) {
    } 
    if (i < 39) {
    }
    dropTables(db);
    onCreate(db);
  }

  @Override
  public void onOpen(final SQLiteDatabase db) {
      super .onOpen(db);
      if (!db.isReadOnly()) {
          // versions of SQLite older than 3.6.19 don't support foreign keys
          // and neither do any version compiled with SQLITE_OMIT_FOREIGN_KEY
          // http://www.sqlite.org/foreignkeys.html#fk_enable
          // 
          // make sure foreign key support is turned on if it's there (should be already, just a double-checker)          
          db.execSQL("PRAGMA foreign_keys=ON;");

          // then we check to make sure they're on 
          // (if this returns no data they aren't even available, so we shouldn't even TRY to use them)
          Cursor c = db.rawQuery("PRAGMA foreign_keys", null);
          if (c.moveToFirst()) {
              int result = c.getInt(0);
              Log.i(this.getClass().getName(), "SQLite foreign key support (1 is on, 0 is off): "  + result);
              if (result==0)
                throw new SQLiteException();
          } else {
              // could use this approach in onCreate, and not rely on foreign keys it not available, etc.
              Log.i(this.getClass().getName(),    "SQLite foreign key support NOT AVAILABLE");
              throw new SQLiteException();
          }
          if (!c.isClosed()) {
              c.close();
          }
      }
  }
  
  public void dropTables(SQLiteDatabase db) {
    db.execSQL("DROP TABLE IF EXISTS " + TBL_ANSWER);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_AGENT);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_GOODS);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_TU);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_ROUTE);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_DEMAND);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_DEMAND_LINE);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_HOST);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_OSNR);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_VERSIONS);
    db.execSQL("DROP TABLE IF EXISTS " + TBL_OPTIONS);
  }

  public static void clearDb(final SQLiteDatabase db) {
    db.execSQL("delete from " + Helper.TBL_DEMAND); // and demand_line cascade
    db.execSQL("delete from sqlite_sequence where name='"+Helper.TBL_DEMAND+"'" );
    db.execSQL("delete from sqlite_sequence where name='"+Helper.TBL_DEMAND_LINE+"'" );
    db.execSQL("delete from " + Helper.TBL_ROUTE);
    db.execSQL("delete from sqlite_sequence where name='"+Helper.TBL_ROUTE+"'" );
    db.execSQL("delete from " + Helper.TBL_AGENT);
    db.execSQL("delete from " + Helper.TBL_OSNR);
    db.execSQL("delete from " + Helper.TBL_HOST);
    db.execSQL("delete from " + Helper.TBL_GOODS); // and tu cascade
    db.execSQL("delete from " + Helper.TBL_OPTIONS);
    db.execSQL("update " + Helper.TBL_VERSIONS + " set " + Helper.TBL_VERSIONS_VALUE + "=0");
  }

}
